package com.example.cloud.service.business.reservation.impl;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.example.cloud.constants.CommonConstants;
import com.example.cloud.constants.StateConstants;
import com.example.cloud.dao.business.reservation.PatientRepository;
import com.example.cloud.dao.business.reservation.ReservationRepository;
import com.example.cloud.dao.business.reservation.VisitRecordRepository;
import com.example.cloud.dao.mongdb.system.MongoOrganRepository;
import com.example.cloud.dao.mongdb.system.MongoUserRepository;
import com.example.cloud.dao.statistics.StatisticsFinancialRepository;
import com.example.cloud.model.business.reservation.Patient;
import com.example.cloud.model.business.reservation.Reservation;
import com.example.cloud.model.business.reservation.VisitRecord;
import com.example.cloud.model.dto.QueryModel;
import com.example.cloud.model.dto.Result;
import com.example.cloud.model.statistics.StatisticsFinancial;
import com.example.cloud.model.system.Organ;
import com.example.cloud.model.system.SystemConfig;
import com.example.cloud.model.system.User;
import com.example.cloud.service.business.reservation.ReservationService;
import com.example.cloud.socket.WebSocketServer;
import com.example.cloud.utils.BusinessUtil;
import com.example.cloud.utils.RequestUtil;
import com.example.cloud.utils.ResultUtil;
import org.apache.commons.lang.StringUtils;
import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang.time.DateUtils;
import org.beetl.sql.core.SQLManager;
import org.beetl.sql.core.SQLReady;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by wangtao on 2018/5/17.
 */
@Service
public class ReservationServiceImpl implements ReservationService {

    @PersistenceContext
    EntityManager em;

    @Autowired
    private SQLManager sqlManager;

    @Autowired
    private ReservationRepository reservationRepository;

    @Autowired
    private VisitRecordRepository visitRecordRepository;

    @Autowired
    private PatientRepository patientRepository;

    @Autowired
    private MongoUserRepository mongoUserRepository;

    @Autowired
    private MongoOrganRepository mongoOrganRepository;

    @Autowired
    private StatisticsFinancialRepository statisticsFinancialRepository;

    @Override
    public List<Reservation> findByAppointment(Long patientId, String startDate, String endDate) {
        StringBuilder sql = new StringBuilder("select * from business_reservation");
        sql.append(" where patient_id = " + patientId);
        sql.append(" and status <> " + StateConstants.RESERVATION_STATUS_CANCEL);
        sql.append(" and appointment BETWEEN '" + startDate + " 00:00:00'");
        sql.append(" and '" + endDate + " 23:59:59'");
        return sqlManager.execute(new SQLReady(sql.toString()), Reservation.class);
    }

    @Override
    @Transactional
    public Result doGenerateAppointment(Map map) {
        Result result = new Result();
        result.setCode(CommonConstants.SUCCESS);
        result.setMsg("预约成功");
        if (!map.containsKey("apptTime") || !map.containsKey("organId") || !map.containsKey("doctorId") || !map.containsKey("nurseId") || !map.containsKey("patientId")) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("请求参数不符合要求['apptTime','organId','doctorId','nurseId','patientId']，以上参数必传");
            return result;
        }
        if (StringUtils.isBlank(map.get("apptTime").toString()) || StringUtils.isBlank(map.get("organId").toString())
                || StringUtils.isBlank(map.get("doctorId").toString()) || StringUtils.isBlank(map.get("nurseId").toString())
                || StringUtils.isBlank(map.get("patientId").toString())) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("请求参数不能为空['apptTime','organId','doctorId','nurseId','patientId']，以上参数必传值");
            return result;
        }
        String apptTime = map.get("apptTime").toString();
        Long organId = Long.valueOf(map.get("organId").toString());
        Long doctorId = Long.valueOf(map.get("doctorId").toString());
        Long nurseId = Long.valueOf(map.get("nurseId").toString());
        Long patientId = Long.valueOf(map.get("patientId").toString());
        String remark = "";
        if (map.containsKey("remark") && StringUtils.isNotBlank(map.get("remark").toString())) {
            remark = map.get("remark").toString();
        }
        Organ organ = mongoOrganRepository.findOne(organId);
        if (organ == null) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("查询诊所信息异常");
            return result;
        }
        Patient patient = patientRepository.findOne(patientId);
        if (patient == null) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("查询患者信息异常");
            return result;
        }
        User doctor = mongoUserRepository.findOne(doctorId);
        if (doctor == null) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("查询医生信息异常");
            return result;
        }
        User nurse = mongoUserRepository.findOne(nurseId);
        if (nurse == null) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("查询护士信息异常");
            return result;
        }
        // 校验患者当天是否已有预约
        boolean ck = checkAppointment(patientId, apptTime);
        if (!ck) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("患者当天已有预约,请勿重复预约");
            return result;
        }
        // 保存预约记录
        Reservation reservation = new Reservation();
        reservation.setPatientId(patient.getId());
        reservation.setPatientName(patient.getName());
        reservation.setMedicalNumber(patient.getMedicalNumber());
        reservation.setStatus(StateConstants.RESERVATION_STATUS_NEW);
        reservation.setType(StateConstants.RESERVATION_TYPE_OLD);
        reservation.setRecordType(StateConstants.RESERVATION_RECORD_TYPE_RESERVATION);
        reservation.setRemind(StateConstants.RESERVATION_REMIND_WECHAT);
        reservation.setProcess(StateConstants.RESERVATION_PROCESS_ARRIVAL);
        reservation.setDoctorId(doctor.getId());
        reservation.setDoctorName(doctor.getUsername());
        reservation.setNurseId(nurse.getId());
        reservation.setNurseName(nurse.getUsername());
        try {
            reservation.setAppointment(DateUtils.parseDate(apptTime, new String[]{"yyyy-MM-dd HH:mm"}));
        } catch (ParseException e) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("预约时间格式不正确，yyyy-MM-dd HH:mm");
            return result;
        }
        reservation.setNote(remark);
        reservation.setAccountId(patient.getAccountId());
        reservation.setUserId(patient.getUserId());
        reservation.setOrganId(organ.getId());
        reservation.setOrganName(organ.getOrganName());
        BusinessUtil.appendRecord(reservation);
        reservation = reservationRepository.save(reservation);
        // 生成接诊记录
        VisitRecord visitRecord = new VisitRecord();
        visitRecord.setReservationId(reservation.getId());
        visitRecord.setPatientId(patient.getId());
        visitRecord.setDoctorId(reservation.getDoctorId());
        visitRecord.setNurseId(reservation.getNurseId());
        visitRecord.setAppointment(reservation.getAppointment());
        visitRecord.setType(reservation.getType());
        visitRecord.setAccountId(reservation.getAccountId());
        visitRecord.setUserId(reservation.getUserId());
        visitRecord.setOrganId(reservation.getOrganId());
        visitRecord.setOrganName(reservation.getOrganName());
        BusinessUtil.appendRecord(visitRecord);
        visitRecordRepository.save(visitRecord);
        return result;
    }

    public boolean checkAppointment(Long patientId, String apptTime) {
        boolean ck = true;
        String ymd = apptTime.substring(0, apptTime.lastIndexOf(" "));
        StringBuilder sql = new StringBuilder("select count(1) as count from business_visit_record");
        sql.append(" where patient_id = " + patientId);
        sql.append(" and DATE_FORMAT(appointment,'%Y-%m-%d') = '" + ymd + "'");
        List<Map> resultMap = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        if (!resultMap.isEmpty() && resultMap.size() > 0) {
            Map map = resultMap.get(0);
            int count = Integer.parseInt(map.get("count").toString());
            if (count > 0) {
                ck = false;
            }
        }
        return ck;
    }

    /**
     * 批量保存预约信息
     *
     * @param records
     */
    @Override
    @Transactional
    public void doSave(List<Reservation> records) throws ParseException {
        User user = RequestUtil.getLoginUser();
        for (Reservation record : records) {
            // 预约记录
            record.setAccountId(user.getAccountId());
            record.setUserId(user.getId());
            record.setOrganId(user.getOrganId());
            record.setOrganName(user.getOrganName());
            record.setStatus(StateConstants.RESERVATION_STATUS_NEW);
            record.setRecordType(StateConstants.RESERVATION_RECORD_TYPE_RESERVATION);
            record.setAppointment(DateUtils.parseDate(record.getAppointmentStr(), CommonConstants.DATE_FORMATS));
            if (record.getDoctorId() != null) {
                User u = mongoUserRepository.findOne(record.getDoctorId());
                if (u != null) {
                    record.setDoctorName(u.getUsername());
                }
            }
            if (record.getNurseId() != null) {
                User u = mongoUserRepository.findOne(record.getNurseId());
                if (u != null) {
                    record.setNurseName(u.getUsername());
                }
            }
            if (record.getExpertId() != null) {
                User u = mongoUserRepository.findOne(record.getExpertId());
                if (u != null) {
                    record.setExpertName(u.getUsername());
                }
            }
            record.setSource(0);
            BusinessUtil.appendRecord(record);
            record = reservationRepository.save(record);

            // 如果是预约到店就生成医护接诊记录
            if (record.getProcess() == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
                VisitRecord visitRecord = new VisitRecord();
                visitRecord.setReservationId(record.getId());
                visitRecord.setPatientId(record.getPatientId());
                visitRecord.setDoctorId(record.getDoctorId());
                visitRecord.setNurseId(record.getNurseId());
                visitRecord.setAppointment(record.getAppointment());
                visitRecord.setType(record.getType());
                visitRecord.setAccountId(record.getAccountId());
                visitRecord.setUserId(record.getUserId());
                visitRecord.setOrganId(record.getOrganId());
                visitRecord.setOrganName(record.getOrganName());
                BusinessUtil.appendRecord(visitRecord);
                visitRecordRepository.save(visitRecord);
            }
        }
    }

    @Override
    public Result doCheckChange(Long id) {
        Result result = new Result();
        result.setCode(CommonConstants.SUCCESS);
        Reservation reservation = reservationRepository.findOne(id);
        if (reservation.getReinventions() >= 4) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("重新预约超过4次，不允许继续预约，请通过客服处理后再次预约");
            return result;
        }
        if (reservation.getProcess() == StateConstants.RESERVATION_PROCESS_PHONE && reservation.getStatus() == StateConstants.RESERVATION_STATUS_RETURN) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("完成回访后不允许重新预约，请核实后再操作");
            return result;
        }
        if (reservation.getProcess() == StateConstants.RESERVATION_PROCESS_ARRIVAL && reservation.getStatus() > StateConstants.RESERVATION_STATUS_RETURN) {
            result.setCode(CommonConstants.FAILURE);
            result.setMsg("到场后不允许重新预约，请核实后再操作");
            return result;
        }
        return result;
    }

    @Override
    @Transactional
    public void doChange(Reservation record) throws ParseException {
        // 记录当前预约的主键
        Long id = record.getId();
        if (record.getProcess() == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            // 删除原预约记录副本
            StringBuilder sql = new StringBuilder("delete from business_visit_record where reservation_id = :id");
            Query query = em.createNativeQuery(sql.toString());
            query.setParameter("id", id);
            query.executeUpdate();
        }
        // 更新原预约状态为已取消
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status,parent_id = :parentId where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
        query.setParameter("parentId", record.getId());
        query.setParameter("id", id);
        query.executeUpdate();
        // 创建新的预约记录
        record.setId(null);
        record.setParentId(null);
        if (record.getDoctorId() != null) {
            User u = mongoUserRepository.findOne(record.getDoctorId());
            if (u != null) {
                record.setDoctorName(u.getUsername());
            }
        }
        if (record.getNurseId() != null) {
            User u = mongoUserRepository.findOne(record.getNurseId());
            if (u != null) {
                record.setNurseName(u.getUsername());
            }
        }
        if (record.getExpertId() != null) {
            User u = mongoUserRepository.findOne(record.getExpertId());
            if (u != null) {
                record.setExpertName(u.getUsername());
            }
        }
        record.setAppointment(DateUtils.parseDate(record.getAppointmentStr(), CommonConstants.DATE_FORMATS));
        record.setStatus(StateConstants.RESERVATION_STATUS_NEW);
        // 改约次数+1
        record.setReinventions(record.getReinventions() + 1);
        record.setSource(0);
        record.setCreationTime(null);
        BusinessUtil.appendRecord(record);
        record = reservationRepository.save(record);
        // 预约到店处理
        if (record.getProcess() == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            // 创建新预约记录副本
            VisitRecord visitRecord = new VisitRecord();
            visitRecord.setReservationId(record.getId());
            visitRecord.setPatientId(record.getPatientId());
            visitRecord.setDoctorId(record.getDoctorId());
            visitRecord.setNurseId(record.getNurseId());
            visitRecord.setAppointment(record.getAppointment());
            visitRecord.setType(record.getType());
            visitRecord.setAccountId(record.getAccountId());
            visitRecord.setUserId(record.getUserId());
            visitRecord.setOrganId(record.getOrganId());
            visitRecord.setOrganName(record.getOrganName());
            BusinessUtil.appendRecord(visitRecord);
            visitRecordRepository.save(visitRecord);
        }
        // 更新历史预约记录最新的预约主键
        sql = new StringBuilder("update business_reservation set parent_id = :parentId1 where parent_id = :parentId2");
        query = em.createNativeQuery(sql.toString());
        query.setParameter("parentId1", record.getId());
        query.setParameter("parentId2", id);
        query.executeUpdate();
    }

    @Override
    public List<Long> doCheckDelete(List<Long> ids) {
        StringBuilder sql = new StringBuilder("select id from business_reservation where id in(:ids) and status in(:status) and delete_flag = 0");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("ids", ids);
        List<Integer> status = new ArrayList<>();
        status.add(StateConstants.RESERVATION_STATUS_NEW);
        status.add(StateConstants.RESERVATION_STATUS_CANCEL);
        query.setParameter("status", status);
        return (List<Long>) query.getResultList();
    }

    @Override
    @Transactional
    public void doDelete(List<Long> ids) {
        // 变更为已删除
        StringBuilder sql = new StringBuilder("delete from business_reservation where id in(:ids)");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("ids", ids);
        query.executeUpdate();
        // 完成后删除副本
        sql = new StringBuilder("delete from business_visit_record where reservation_id in(:ids)");
        query = em.createNativeQuery(sql.toString());
        query.setParameter("ids", ids);
        query.executeUpdate();
    }

    /**
     * 查询今日接待
     *
     * @return
     */
    @Override
    public Result searchReservation(Map params) {
        User user = RequestUtil.getLoginUser();
        StringBuilder sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse,t2.score as score from business_reservation t1,basic_patient t2");
        // 关联字段
        sql.append(" where t1.patient_id = t2.id");
        // 账户过滤
        sql.append(" and t1.account_id = " + user.getAccountId());
        // 预约到店
        sql.append(" and t1.process = " + StateConstants.RESERVATION_PROCESS_ARRIVAL);
        // 过滤医生
        if (params.containsKey("doctorId") && params.get("doctorId") != null && StringUtils.isNotBlank(params.get("doctorId").toString())) {
            sql.append(" and t1.doctor_id = " + params.get("doctorId").toString());
        } else {
            // 非管理员用户需要过滤数据权限
            if (user.getIsAdmin() != 1 && user.getJob() != null) {
                if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                    // 专家查询
                    if (user.getIsExpert() == StateConstants.YES) {
                        sql.append(" and (t1.doctor_id = " + user.getId() + " or t1.expert_id = " + user.getId() + ")");
                    } else {
                        sql.append(" and t1.doctor_id = " + user.getId());
                    }
                }
            }
        }
        // 过滤护士
        if (params.containsKey("nurseId") && params.get("nurseId") != null && StringUtils.isNotBlank(params.get("nurseId").toString())) {
            sql.append(" and t1.nurse_id = " + params.get("nurseId").toString());
        } else {
            // 非管理员用户需要过滤数据权限
            if (user.getIsAdmin() != 1 && user.getJob() != null) {
                if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                    sql.append(" and t1.nurse_id = " + user.getId());
                }
            }
        }
        // 医生查询医生完成状态小于治疗完成
        if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
            sql.append(" and t1.doctor_status <= " + StateConstants.TREATING);
            // 护士查询护士完成状态小于治疗完成
        } else if (user.getJob() == StateConstants.USER_JOB_NURSE) {
            sql.append(" and t1.nurse_status = " + StateConstants.WAIT_TREAT);
        } else {
            // 非管理员查询客服完成状态小于治疗完成
            if (user.getIsAdmin() != 1) {
                sql.append(" and t1.customer_status = " + StateConstants.WAIT_TREAT);
            }
        }
        String dateStr = DateFormatUtils.format(new Date(), "yyyy-MM-dd");
        String startDate = dateStr + " 00:00:00";
        String endDate = dateStr + " 23:59:59";
        // 查询当天的，日期格式yyyy-MM-dd
        sql.append(" and t1.appointment between '" + startDate + "'" + " and '" + endDate + "'");
        // 查询未完成和未取消的
        sql.append(" and t1.status <= " + StateConstants.RESERVATION_STATUS_ADMISSION);
        // 查询改约3次内的，包括3次
        sql.append(" and t1.reinventions <= 4");
        // 过滤患者名称
        if (params.containsKey("patientName") && params.get("patientName") != null) {
            String patientName = params.get("patientName").toString();
            if (StringUtils.isNotBlank(patientName)) {
                sql.append(" and t1.patient_name like '%" + patientName + "%'");
            }
        }
        // 过滤病历号
        if (params.containsKey("medicalNumber") && params.get("medicalNumber") != null) {
            String medicalNumber = params.get("medicalNumber").toString();
            if (StringUtils.isNotBlank(medicalNumber)) {
                sql.append(" and t2.medical_number like '%" + medicalNumber + "%'");
            }
        }
        // 只查询最新的预约(非重新预约)
        sql.append(" and t1.parent_id is null");
        // 查询未删除的
        sql.append(" and t1.delete_flag = 0");
        // 按预约时间排序
        sql.append(" order by t1.appointment");
        QueryModel q = JSON.parseObject(JSON.toJSONString(params.get("queryModel")), QueryModel.class);
        List<Map> l1 = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int e = q.getPageNumber() * q.getPageSize() > l1.size() ? l1.size() : q.getPageNumber() * q.getPageSize();
        int s = (q.getPageNumber() - 1) * q.getPageSize();
        List<Map> l2 = new ArrayList();
        for (int i = s; i < e; i++) {
            l2.add(l1.get(i));
        }
        Map<String, Object> p = new HashMap<String, Object>(3);
        p.put("pageNumber", q.getPageNumber());
        p.put("pageSize", q.getPageSize());
        p.put("totalSize", l1.size());
        return ResultUtil.success(l2, p);
    }

    @Override
    public Result searchVisit(Map params) {
        List<Map> resultList = new ArrayList<>();
        User user = RequestUtil.getLoginUser();
        Date today = new Date();
        StringBuilder sql = null;
        String process = params.get("process").toString();
        if ("".equals(process) || "1".equals(process)) {
            // 查询今日回访
            sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse,t2.score as score from business_reservation t1,basic_patient t2");
            // 关联字段
            sql.append(" where t1.patient_id = t2.id");
            // 账户过滤
            sql.append(" and t1.account_id = " + user.getAccountId());
            // 查询电话随访
            sql.append(" and t1.process = " + StateConstants.RESERVATION_PROCESS_PHONE);
            // 医生过滤
            if (params.containsKey("doctorId") && params.get("doctorId") != null && StringUtils.isNotBlank(params.get("doctorId").toString())) {
                sql.append(" and t1.doctor_id = " + params.get("doctorId").toString());
            } else {
                // 非管理员用户需要过滤数据权限
                if (user.getIsAdmin() != 1 && user.getJob() != null) {
                    if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                        // 专家查询
                        if (user.getIsExpert() == StateConstants.YES) {
                            sql.append(" and (t1.doctor_id = " + user.getId() + " or t1.expert_id = " + user.getId() + ")");
                        } else {
                            sql.append(" and t1.doctor_id = " + user.getId());
                        }
                    }
                }
            }
            // 护士过滤
            if (params.containsKey("nurseId") && params.get("nurseId") != null && StringUtils.isNotBlank(params.get("nurseId").toString())) {
                sql.append(" and t1.nurse_id = " + params.get("nurseId").toString());
            } else {
                // 非管理员用户需要过滤数据权限
                if (user.getIsAdmin() != 1 && user.getJob() != null) {
                    if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                        sql.append(" and t1.nurse_id = " + user.getId());
                    }
                }
            }
            // 查询未完成和未取消的
            sql.append(" and t1.status = " + StateConstants.RESERVATION_STATUS_NEW);
            // 查询当天的，日期格式yyyy-MM-dd
            String dateStr = DateFormatUtils.format(today, "yyyy-MM-dd");
            String startDate = dateStr + " 00:00:00";
            String endDate = dateStr + " 23:59:59";
            sql.append(" and t1.appointment between '" + startDate + "'" + " and '" + endDate + "'");
            // 查询改约3次内的，包括3次
            sql.append(" and t1.reinventions <= 4");
            // 过滤患者名称
            if (params.containsKey("patientName") && params.get("patientName") != null) {
                String patientName = params.get("patientName").toString();
                if (StringUtils.isNotBlank(patientName)) {
                    sql.append(" and t1.patient_name like '%" + patientName + "%'");
                }
            }
            // 过滤病历号
            if (params.containsKey("medicalNumber") && params.get("medicalNumber") != null) {
                String medicalNumber = params.get("medicalNumber").toString();
                if (StringUtils.isNotBlank(medicalNumber)) {
                    sql.append(" and t2.medical_number like '%" + medicalNumber + "%'");
                }
            }
            // 查询未删除的
            sql.append(" and t1.delete_flag = 0");
            // 按预约时间排序
            sql.append(" order by t1.appointment");
            resultList.addAll(sqlManager.execute(new SQLReady(sql.toString()), Map.class));
        }
        if ("".equals(process) || "2".equals(process)) {
            // 查询明日预约到店
            sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse,t2.score as score from business_reservation t1,basic_patient t2");
            // 关联字段
            sql.append(" where t1.patient_id = t2.id");
            // 账户过滤
            sql.append(" and t1.account_id = " + user.getAccountId());
            // 查询今日接待
            sql.append(" and t1.process = " + StateConstants.RESERVATION_PROCESS_ARRIVAL);
            // 医生过滤
            if (params.containsKey("doctorId") && params.get("doctorId") != null && StringUtils.isNotBlank(params.get("doctorId").toString())) {
                sql.append(" and t1.doctor_id = " + params.get("doctorId").toString());
            } else {
                // 非管理员用户需要过滤数据权限
                if (user.getIsAdmin() != 1 && user.getJob() != null) {
                    if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                        // 专家查询
                        if (user.getIsExpert() == StateConstants.YES) {
                            sql.append(" and (t1.doctor_id = " + user.getId() + " or t1.expert_id = " + user.getId() + ")");
                        } else {
                            sql.append(" and t1.doctor_id = " + user.getId());
                        }
                    }
                }
            }
            // 护士过滤
            if (params.containsKey("nurseId") && params.get("nurseId") != null && StringUtils.isNotBlank(params.get("nurseId").toString())) {
                sql.append(" and t1.nurse_id = " + params.get("nurseId").toString());
            } else {
                // 非管理员用户需要过滤数据权限
                if (user.getIsAdmin() != 1 && user.getJob() != null) {
                    if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                        sql.append(" and t1.nurse_id = " + user.getId());
                    }
                }
            }
            // 查询未完成和未取消的
            sql.append(" and t1.status = " + StateConstants.RESERVATION_STATUS_NEW);
            // 查询当天的，日期格式yyyy-MM-dd
            Date tomorrow = DateUtils.addDays(today, 1);
            String dateStr = DateFormatUtils.format(tomorrow, "yyyy-MM-dd");
            String startDate = dateStr + " 00:00:00";
            String endDate = dateStr + " 23:59:59";
            sql.append(" and t1.appointment between '" + startDate + "'" + " and '" + endDate + "'");
            // 查询改约3次内的，包括3次
            sql.append(" and t1.reinventions <= 3");
            // 患者姓名过滤
            if (params.containsKey("patientName") && params.get("patientName") != null) {
                String patientName = params.get("patientName").toString();
                if (StringUtils.isNotBlank(patientName)) {
                    sql.append(" and t1.patient_name like '%" + patientName + "%'");
                }
            }
            // 病历号过滤
            if (params.containsKey("medicalNumber") && params.get("medicalNumber") != null) {
                String medicalNumber = params.get("medicalNumber").toString();
                if (StringUtils.isNotBlank(medicalNumber)) {
                    sql.append(" and t2.medical_number like '%" + medicalNumber + "%'");
                }
            }
            // 只查询最新的预约(非重新预约)
            sql.append(" and t1.parent_id is null");
            // 查询未删除的
            sql.append(" and t1.delete_flag = 0");
            // 按预约时间排序
            sql.append(" order by t1.appointment");
            resultList.addAll(sqlManager.execute(new SQLReady(sql.toString()), Map.class));
        }
        QueryModel q = JSON.parseObject(JSON.toJSONString(params.get("queryModel")), QueryModel.class);
        int e = q.getPageNumber() * q.getPageSize() > resultList.size() ? resultList.size() : q.getPageNumber() * q.getPageSize();
        int s = (q.getPageNumber() - 1) * q.getPageSize();
        List<Map> l2 = new ArrayList();
        for (int i = s; i < e; i++) {
            l2.add(resultList.get(i));
        }
        Map<String, Object> p = new HashMap<String, Object>(3);
        p.put("pageNumber", q.getPageNumber());
        p.put("pageSize", q.getPageSize());
        p.put("totalSize", resultList.size());
        return ResultUtil.success(l2, p);
    }

    @Override
    public Result searchComplete(int process, QueryModel q) {
        User user = RequestUtil.getLoginUser();
        StringBuilder sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse from business_reservation t1,basic_patient t2");
        sql.append(" where t1.patient_id = t2.id");
        // 账户过滤
        sql.append(" and t1.account_id = " + user.getAccountId());
        // 处理类型
        sql.append(" and t1.process = " + process);
        // 非管理员用户需要过滤数据权限
        if (user.getIsAdmin() != 1 && user.getJob() != null) {
            if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                sql.append(" and t1.nurse_id = " + user.getId());
            } else if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                // 专家查询
                if (user.getIsExpert() == StateConstants.YES) {
                    sql.append(" and (t1.doctor_id = " + user.getId() + " or t1.expert_id = " + user.getId() + ")");
                } else {
                    sql.append(" and t1.doctor_id = " + user.getId());
                }
            }
        }
        // 电话随访
        if (process == StateConstants.RESERVATION_PROCESS_PHONE) {
            sql.append(" and t1.status = " + StateConstants.RESERVATION_STATUS_RETURN);
            // 预约到店
        } else if (process == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            sql.append(" and t1.status = " + StateConstants.RESERVATION_STATUS_COMPLETE);
        }
        String dateStr = DateFormatUtils.format(new Date(), "yyyy-MM-dd");
        String startDate = dateStr + " 00:00:00";
        String endDate = dateStr + " 23:59:59";
        // 查询当天的，日期格式yyyy-MM-dd
        sql.append(" and t1.appointment between '" + startDate + "'" + " and '" + endDate + "'");
        // 查询未删除的
        sql.append(" and t1.delete_flag = 0");
        List<Map> l1 = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int e = q.getPageNumber() * q.getPageSize() > l1.size() ? l1.size() : q.getPageNumber() * q.getPageSize();
        int s = (q.getPageNumber() - 1) * q.getPageSize();
        List<Map> l2 = new ArrayList();
        for (int i = s; i < e; i++) {
            l2.add(l1.get(i));
        }
        Map<String, Object> p = new HashMap<String, Object>(3);
        p.put("pageNumber", q.getPageNumber());
        p.put("pageSize", q.getPageSize());
        p.put("totalSize", l1.size());
        return ResultUtil.success(l2, p);
    }

    @Override
    public Result searchUndone(int process, QueryModel q) {
        User user = RequestUtil.getLoginUser();
        StringBuilder sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse from business_reservation t1,basic_patient t2");
        sql.append(" where t1.patient_id = t2.id");
        // 账户过滤
        sql.append(" and t1.account_id = " + user.getAccountId());
        // 处理方式
        sql.append(" and t1.process = " + process);
        // 非管理员用户需要过滤数据权限
        if (user.getIsAdmin() != 1 && user.getJob() != null) {
            if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                sql.append(" and t1.nurse_id = " + user.getId());
            } else if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                // 专家查询
                if (user.getIsExpert() == StateConstants.YES) {
                    sql.append(" and (t1.doctor_id = " + user.getId() + " or t1.expert_id = " + user.getId() + ")");
                } else {
                    sql.append(" and t1.doctor_id = " + user.getId());
                }
            }
        }
        // 电话随访
        if (process == StateConstants.RESERVATION_PROCESS_PHONE) {
            // 查询未回访的
            sql.append(" and t1.status = " + StateConstants.RESERVATION_STATUS_NEW);
            // 预约到店
        } else if (process == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            // 医生查询医生完成状态小于治疗完成
            if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
                sql.append(" and t1.doctor_status <= " + StateConstants.TREATING);
                // 护士查询护士完成状态小于治疗完成
            } else if (user.getJob() == StateConstants.USER_JOB_NURSE) {
                sql.append(" and t1.nurse_status = " + StateConstants.WAIT_TREAT);
            } else {
                // 非管理员查询客服完成状态小于治疗完成
                if (user.getIsAdmin() != 1) {
                    sql.append(" and t1.customer_status = " + StateConstants.WAIT_TREAT);
                }
            }
        }
        // 预约到店需要过滤掉已取消的和被支持的
        if (process == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            // 查询已完成之前的
            sql.append(" and t1.status <= " + StateConstants.RESERVATION_STATUS_ADMISSION);
        }
        Date today = new Date();
        Date yesterday = DateUtils.addDays(today, -1);
        String endDate = DateFormatUtils.format(yesterday, "yyyy-MM-dd") + " 23:59:59";
        // 查询昨天及之前的，日期格式yyyy-MM-dd
        sql.append(" and t1.appointment < '" + endDate + "'");
        // 查询未删除的
        sql.append(" and t1.delete_flag = 0");
        List<Map> l1 = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int e = q.getPageNumber() * q.getPageSize() > l1.size() ? l1.size() : q.getPageNumber() * q.getPageSize();
        int s = (q.getPageNumber() - 1) * q.getPageSize();
        List<Map> l2 = new ArrayList();
        for (int i = s; i < e; i++) {
            l2.add(l1.get(i));
        }
        Map<String, Object> p = new HashMap<String, Object>(3);
        p.put("pageNumber", q.getPageNumber());
        p.put("pageSize", q.getPageSize());
        p.put("totalSize", l1.size());
        return ResultUtil.success(l2, p);
    }

    @Override
    public Result searchRefuseTreat(QueryModel q) {
        User user = RequestUtil.getLoginUser();
        StringBuilder sql = new StringBuilder("select t1.*,t2.name as patient_name,t2.medical_number as medical_number,t2.phone as phone,t2.doctor_name as competent_doctor,t2.nurse_name as competent_nurse from business_reservation t1,basic_patient t2");
        // 关联字段
        sql.append(" where t1.patient_id = t2.id");
        // 账户过滤
        sql.append(" and t1.account_id = " + user.getAccountId());
        // 预约到店
        sql.append(" and t1.process = " + StateConstants.RESERVATION_PROCESS_ARRIVAL);
        // 重新预约次数大于3
        sql.append(" and t1.reinventions > 4");
        // 查询未完成和未取消的
        sql.append(" and t1.status <= " + StateConstants.RESERVATION_STATUS_ADMISSION);
        // 查询未删除的
        sql.append(" and t1.delete_flag = 0");
        // 按预约时间排序
        sql.append(" order by t1.appointment");
        List<Map> l1 = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int e = q.getPageNumber() * q.getPageSize() > l1.size() ? l1.size() : q.getPageNumber() * q.getPageSize();
        int s = (q.getPageNumber() - 1) * q.getPageSize();
        List<Map> l2 = new ArrayList();
        for (int i = s; i < e; i++) {
            l2.add(l1.get(i));
        }
        Map<String, Object> p = new HashMap<String, Object>(3);
        p.put("pageNumber", q.getPageNumber());
        p.put("pageSize", q.getPageSize());
        p.put("totalSize", l1.size());
        return ResultUtil.success(l2, p);
    }

    @Override
    @Transactional
    public void doSupport(Reservation record) {
        // 变更预约记录状态
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("status", StateConstants.RESERVATION_STATUS_SUPPORTED);
        query.setParameter("id", record.getId());
        query.executeUpdate();

        // 生成支持记录
        record.setId(null);
        record.setParentId(record.getId());
        record.setRecordType(StateConstants.RESERVATION_RECORD_TYPE_SUPPORT);
        record.setCreationTime(null);
        BusinessUtil.appendRecord(record);
        reservationRepository.save(record);
    }

    @Override
    @Transactional
    public void doAppointment(Reservation record) {
        // 变更预约记录状态
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
        query.setParameter("id", record.getId());
        query.executeUpdate();

        // 关联最原始的预约记录
        if (record.getParentId() == null) {
            record.setParentId(record.getId());
        }
        // 改约次数+1
        record.setReinventions(record.getReinventions() + 1);
        // 初始化状态
        record.setStatus(StateConstants.RESERVATION_STATUS_NEW);
        record.setId(null);
        record.setCreationTime(null);
        BusinessUtil.appendRecord(record);
        reservationRepository.save(record);
    }

    @Override
    public boolean doCheckCancel(Long id) {
        StringBuilder sql = new StringBuilder("select * from business_reservation where id = :id and status <= :status");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_RETURN);
        int count = query.getResultList().size();
        return count > 0;
    }

    @Override
    @Transactional
    public void doCancel(Long id) {
        // 变更预约状态
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
        query.executeUpdate();
        // 完成后删除副本
        sql = new StringBuilder("delete from business_visit_record where reservation_id = :id");
        query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.executeUpdate();
    }

    @Override
    public boolean doCheckArrived(Long id) {
        StringBuilder sql = new StringBuilder("select * from business_reservation where id = :id and status in(:status)");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        List<Integer> status = new ArrayList<>();
        status.add(StateConstants.RESERVATION_STATUS_NEW);
        status.add(StateConstants.RESERVATION_STATUS_RETURN);
        query.setParameter("status", status);
        int count = query.getResultList().size();
        return count > 0;
    }

    @Override
    @Transactional
    public void doArrived(Long id) {
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_ARRIVED);
        query.executeUpdate();

        try {
            List<Long> clientIds = new ArrayList<>();
            Reservation r = reservationRepository.findOne(id);
            clientIds.add(r.getDoctorId());
            clientIds.add(r.getNurseId());
            StringBuilder s = new StringBuilder();
            s.append("<div style='text-align: left'>患者[" + r.getPatientName() + "]已到场<br/>");
            if (r.getMedicalNumber() != null) {
                s.append("病历号[" + r.getMedicalNumber() + "]<br/>");
            }
            s.append("预约时间：" + DateFormatUtils.format(r.getAppointment(), "yyyy-MM-dd HH:mm") + "<br/>预约备注：" + r.getNote());
            s.append("</div>");
//            BusinessUtil.sendMessage(s.toString(), clientIds, StateConstants.MESSAGE_TYPE_SYSTEM);
            JSONObject result = new JSONObject();
            JSONObject data = new JSONObject();
            data.put("system", true);
            data.put("id", -1);
            data.put("type", "friend");
            data.put("content", s.toString());
            result.put("emit", "chatMessage");
            result.put("data", data);
            WebSocketServer.send(result, clientIds);

            // 新诊人数统计
            int type = r.getType();
            // 新诊
            if(type == StateConstants.RESERVATION_TYPE_NEW){
                // 医生
                sql = new StringBuilder("select * from statistics_financial where user_id = :id and DATE_FORMAT(creation_time,'%Y-%m-%d') = :time");
                Query nq = em.createNativeQuery(sql.toString(), StatisticsFinancial.class);
                nq.setParameter("id", r.getDoctorId());
                nq.setParameter("time", DateFormatUtils.format(new Date(), "yyyy-MM-dd"));
                List<StatisticsFinancial> rs = nq.getResultList();
                if(!rs.isEmpty()){
                    sql = new StringBuilder("update statistics_financial set new_number = new_number + 1 where id = :id");
                    nq = em.createNativeQuery(sql.toString());
                    nq.setParameter("id", rs.get(0).getId());
                    nq.executeUpdate();
                }else{
                    StatisticsFinancial sf = new StatisticsFinancial();
                    sf.setUserId(r.getDoctorId());
                    sf.setUserName(r.getDoctorName());
                    sf.setNewNumber(1);
                    sf.setAccountId(r.getAccountId());
                    sf.setOrganId(r.getOrganId());
                    sf.setOrganName(r.getOrganName());
                    BusinessUtil.appendRecord(sf);
                    statisticsFinancialRepository.save(sf);
                }
                // 护士
                sql = new StringBuilder("select * from statistics_financial where user_id = :id and DATE_FORMAT(creation_time,'%Y-%m-%d') = :time");
                nq = em.createNativeQuery(sql.toString(), StatisticsFinancial.class);
                nq.setParameter("id", r.getNurseId());
                nq.setParameter("time", DateFormatUtils.format(new Date(), "yyyy-MM-dd"));
                rs = nq.getResultList();
                if(!rs.isEmpty()){
                    sql = new StringBuilder("update statistics_financial set new_number = new_number + 1 where id = :id");
                    nq = em.createNativeQuery(sql.toString());
                    nq.setParameter("id", rs.get(0).getId());
                    nq.executeUpdate();
                }else{
                    StatisticsFinancial sf = new StatisticsFinancial();
                    sf.setUserId(r.getNurseId());
                    sf.setUserName(r.getNurseName());
                    sf.setNewNumber(1);
                    sf.setAccountId(r.getAccountId());
                    sf.setOrganId(r.getOrganId());
                    sf.setOrganName(r.getOrganName());
                    BusinessUtil.appendRecord(sf);
                    statisticsFinancialRepository.save(sf);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    public boolean doCheckAdmission(Long id) {
        StringBuilder sql = new StringBuilder("select * from business_reservation where id = :id and status >= :status");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_ARRIVED);
        int count = query.getResultList().size();
        return count > 0;
    }

    @Override
    @Transactional(propagation = Propagation.REQUIRED)
    public void doAdmission(Long id) {
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_ADMISSION);
        query.executeUpdate();
    }

    @Override
    @Transactional
    public void doTreatComplete(Long id) {
        StringBuilder sql = new StringBuilder("update business_reservation set nurse_status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.TREAT_COMPLETE);
        query.executeUpdate();
        // 校验是否可以更新预约为完成
        sql = new StringBuilder("update business_reservation set status = :status where id = :id and doctor_status = :checkStatus and nurse_status = :checkStatus and customer_status = :checkStatus");
        query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_COMPLETE);
        query.setParameter("checkStatus", StateConstants.TREAT_COMPLETE);
        int c = query.executeUpdate();
        if (c == 1) {
            // 完成后删除副本
            sql = new StringBuilder("delete from business_visit_record where reservation_id = :id");
            query = em.createNativeQuery(sql.toString());
            query.setParameter("id", id);
            query.executeUpdate();
        }
    }

    @Override
    @Transactional
    public void doVisitComplete(Long id) {
        // 更新当前预约的医生完成状态状态
        StringBuilder sql = new StringBuilder("update business_reservation set doctor_status = :status where id = :id");
        Query nq = em.createNativeQuery(sql.toString());
        nq.setParameter("status", StateConstants.TREAT_COMPLETE);
        nq.setParameter("id", id);
        nq.executeUpdate();
        Reservation reservation = reservationRepository.findOne(id);
        // 如果已排班
        if (reservation.getIsSchedule() == 1) {
            // 更新治疗任务状态
            sql = new StringBuilder("update treatment_task set `status` = :status where reservation_id = :id");
            nq = em.createNativeQuery(sql.toString());
            nq.setParameter("status", StateConstants.TREAT_COMPLETE);
            nq.setParameter("id", id);
            nq.executeUpdate();
        }
        // 校验是否可以更新预约为完成
        sql = new StringBuilder("update business_reservation set status = :status where id = :id and doctor_status = :checkStatus and nurse_status = :checkStatus and customer_status = :checkStatus");
        nq = em.createNativeQuery(sql.toString());
        nq.setParameter("id", id);
        nq.setParameter("status", StateConstants.RESERVATION_STATUS_COMPLETE);
        nq.setParameter("checkStatus", StateConstants.TREAT_COMPLETE);
        int c = nq.executeUpdate();
        if (c == 1) {
            // 完成后删除副本
            sql = new StringBuilder("delete from business_visit_record where reservation_id = :id");
            nq = em.createNativeQuery(sql.toString());
            nq.setParameter("id", id);
            nq.executeUpdate();
        }
    }

    @Override
    public boolean doCheckComplete(Long id) {
        StringBuilder sql = new StringBuilder("select * from business_reservation where id = :id and status = :status");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_ADMISSION);
        int count = query.getResultList().size();
        return count > 0;
    }

    @Override
    @Transactional
    public void doComplete(Long id) {
        // 更新客服完成状态
        StringBuilder sql = new StringBuilder("update business_reservation set customer_status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.TREAT_COMPLETE);
        query.executeUpdate();
        // 校验是否可以更新预约为完成
        sql = new StringBuilder("update business_reservation set status = :status where id = :id and doctor_status = :checkStatus and nurse_status = :checkStatus and customer_status = :checkStatus");
        query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_COMPLETE);
        query.setParameter("checkStatus", StateConstants.TREAT_COMPLETE);
        int c = query.executeUpdate();
        if (c == 1) {
            // 完成后删除副本
            sql = new StringBuilder("delete from business_visit_record where reservation_id = :id");
            query = em.createNativeQuery(sql.toString());
            query.setParameter("id", id);
            query.executeUpdate();
        }
    }

    @Override
    @Transactional
    public void doVisitConfirm(Long id) {
        StringBuilder sql = new StringBuilder("update business_reservation set status = :status where id = :id");
        Query query = em.createNativeQuery(sql.toString());
        query.setParameter("id", id);
        query.setParameter("status", StateConstants.RESERVATION_STATUS_RETURN);
        query.executeUpdate();
    }

    @Override
    @Transactional
    public void doEditNote(Long id, String note) {
        StringBuilder sql = new StringBuilder("update business_reservation set note = :note where id = :id");
        Query nativeQuery = em.createNativeQuery(sql.toString());
        nativeQuery.setParameter("note", note);
        nativeQuery.setParameter("id", id);
        nativeQuery.executeUpdate();
    }

    @Override
    public Result doCheckAppointment(Reservation record, SystemConfig config) throws ParseException {
        Result result = new Result();
        result.setCode(CommonConstants.SUCCESS);
        result.setMsg("操作成功");
        // 校验患者本周是否已有预约
        String d1 = BusinessUtil.getFirstOfWeek(record.getAppointmentStr());
        String d2 = BusinessUtil.getLastOfWeek(record.getAppointmentStr());
        // 开启校验并且是预约到店才校验
        if (config.getCheck1() == 1 && record.getProcess() == StateConstants.RESERVATION_PROCESS_ARRIVAL) {
            boolean check = checkAppointment(record.getId(), record.getPatientId(), d1, d2);
            if (!check) {
                result.setCode(CommonConstants.FAILURE);
                result.setMsg("患者本周已预约过");
                return result;
            }
        }
        User doctor = mongoUserRepository.findOne(record.getDoctorId());
        User nurse = mongoUserRepository.findOne(record.getNurseId());
        if (record.getType() == StateConstants.RESERVATION_TYPE_OLD) {
            // 校验护士是否已有预约
            StringBuilder sql = new StringBuilder("select * from business_visit_record where nurse_id = :nid and appointment = :time and type = :type and STATUS != :status");
            if (record.getId() != null) {
                sql.append(" and reservation_id != :id");
            }
            Query nativeQuery = em.createNativeQuery(sql.toString());
            nativeQuery.setParameter("nid", record.getNurseId());
            nativeQuery.setParameter("time", record.getAppointmentStr());
            nativeQuery.setParameter("type", StateConstants.RESERVATION_TYPE_OLD);
            nativeQuery.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
            if (record.getId() != null) {
                nativeQuery.setParameter("id", record.getId());
            }
            // 护士复诊预约数
            int i = nativeQuery.getResultList().size();
            // 如果大于系统限制，无法预约
            if (config.getNurseReceptionOld() != null && config.getNurseReceptionOld().intValue() > 0 && (i + 1) > config.getNurseReceptionOld().intValue()) {
                result.setCode(CommonConstants.FAILURE);
                result.setMsg("系统检测到当前预约为复诊预约，系统限制护士每" + config.getNurseInterval() + "分钟只能接待" + config.getNurseReceptionOld().intValue() + "位复诊，您所约的护士【" + nurse.getUsername() + "】当前预约时间已有" + i + "条预约，请查询预约列表确认");
                return result;
            }
            // 校验医生预约人次
            sql = new StringBuilder("select * from business_visit_record where doctor_id = :did and appointment = :time and type = :type and STATUS != :status");
            if (record.getId() != null) {
                sql.append(" and reservation_id != :id");
            }
            nativeQuery = em.createNativeQuery(sql.toString());
            nativeQuery.setParameter("did", record.getDoctorId());
            nativeQuery.setParameter("time", record.getAppointmentStr());
            nativeQuery.setParameter("type", StateConstants.RESERVATION_TYPE_OLD);
            nativeQuery.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
            if (record.getId() != null) {
                nativeQuery.setParameter("id", record.getId());
            }
            // 医生复诊预约数
            i = nativeQuery.getResultList().size();
            // 如果大于系统限制，则无法预约
            if (config.getDoctorReceptionOld() != null && config.getDoctorReceptionOld().intValue() > 0 && (i + 1) > config.getDoctorReceptionOld().intValue()) {
                result.setCode(CommonConstants.FAILURE);
                result.setMsg("系统检测到当前预约为复诊预约，系统限制医生每" + config.getDoctorInterval() + "分钟只能接待" + config.getDoctorReceptionOld().intValue() + "位复诊，您所约的医生【" + doctor.getUsername() + "】当前预约时间已有" + i + "条预约，请查询预约列表确认");
                return result;
            }
        } else if (record.getType() == StateConstants.RESERVATION_TYPE_NEW) {
            // 新诊校验医生，和护士当前预约次数
            StringBuilder sql = new StringBuilder("select * from business_visit_record where doctor_id = :did and appointment = :time and type = :type and STATUS != :status");
            if (record.getId() != null) {
                sql.append(" and reservation_id != :id");
            }
            Query nativeQuery = em.createNativeQuery(sql.toString());
            nativeQuery.setParameter("did", record.getDoctorId());
            nativeQuery.setParameter("time", record.getAppointmentStr());
            nativeQuery.setParameter("type", StateConstants.RESERVATION_TYPE_NEW);
            nativeQuery.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
            if (record.getId() != null) {
                nativeQuery.setParameter("id", record.getId());
            }
            // 医生新诊预约数
            int i = nativeQuery.getResultList().size();
            // 如果大于系统限制，无法预约
            if (config.getDoctorReceptionNew() != null && config.getDoctorReceptionNew().intValue() > 0 && (i + 1) > config.getDoctorReceptionNew().intValue()) {
                result.setCode(CommonConstants.FAILURE);
                result.setMsg("系统检测到当前预约为新诊预约，系统限制医生每" + config.getDoctorInterval() + "分钟只能接待" + config.getDoctorReceptionNew().intValue() + "位新诊，您所约的医生【" + doctor.getUsername() + "】当前预约时间已有" + i + "条预约，请查询预约列表确认");
                return result;
            }
            sql = new StringBuilder("select * from business_visit_record where nurse_id = :nid and appointment = :time and type = :type and STATUS != :status");
            if (record.getId() != null) {
                sql.append(" and reservation_id != :id");
            }
            nativeQuery = em.createNativeQuery(sql.toString());
            nativeQuery.setParameter("nid", record.getNurseId());
            nativeQuery.setParameter("time", record.getAppointmentStr());
            nativeQuery.setParameter("type", StateConstants.RESERVATION_TYPE_NEW);
            nativeQuery.setParameter("status", StateConstants.RESERVATION_STATUS_CANCEL);
            if (record.getId() != null) {
                nativeQuery.setParameter("id", record.getId());
            }
            // 护士新诊预约数
            i = nativeQuery.getResultList().size();
            // 如果大于系统限制，无法预约
            if (config.getNurseReceptionNew() != null && config.getNurseReceptionNew().intValue() > 0 && (i + 1) > config.getNurseReceptionNew().intValue()) {
                result.setCode(CommonConstants.FAILURE);
                result.setMsg("系统检测到当前预约为新诊预约，系统限制护士每" + config.getNurseInterval() + "分钟只能接待" + config.getNurseReceptionNew().intValue() + "位新诊，您所约的护士【" + nurse.getUsername() + "】当前预约时间已有" + i + "条预约，请查询预约列表确认");
                return result;
            }
        }
        return result;
    }

    /**
     * 校验患者本周是否已有预约
     *
     * @param patientId
     * @param startTime
     * @param endTime
     * @return
     */
    public boolean checkAppointment(Long id, Long patientId, String startTime, String endTime) {
        boolean check = true;
        StringBuilder sql = new StringBuilder("select count(1) as count from business_visit_record");
        sql.append(" where patient_id = " + patientId);
        sql.append(" and appointment between '" + startTime + "' and '" + endTime + "'");
        if (id != null) {
            sql.append(" and reservation_id != " + id);
        }
        List<Map> list = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        int n1 = Integer.parseInt(list.get(0).get("count").toString());
        if (n1 > 0) {
            check = false;
        }
        return check;
    }

    @Override
    public void sendPreview() {
        User user = RequestUtil.getLoginUser();
        StringBuilder html = new StringBuilder();
        StringBuilder sql = new StringBuilder("select DATE_FORMAT(t1.appointment,'%H点%m分') appointment,t1.note note,t2.name as patientName,t2.medical_number as medicalNumber from business_reservation t1,basic_patient t2");
        // 关联字段
        sql.append(" where t1.patient_id = t2.id");
        // 账户过滤
        sql.append(" and t1.account_id = " + user.getAccountId());
        if (user.getJob() == StateConstants.USER_JOB_DOCTOR) {
            sql.append(" and t1.doctor_id = " + user.getId());
        } else if (user.getJob() == StateConstants.USER_JOB_NURSE) {
            sql.append(" and t1.nurse_id = " + user.getId());
        } else {
            return;
        }
        sql.append(" and t1.process = " + StateConstants.RESERVATION_PROCESS_ARRIVAL);
        String dateStr = DateFormatUtils.format(new Date(), "yyyy-MM-dd");
        String startDate = dateStr + " 00:00:00";
        String endDate = dateStr + " 23:59:59";
        // 查询当天的，日期格式yyyy-MM-dd
        sql.append(" and t1.appointment between '" + startDate + "'" + " and '" + endDate + "'");
        // 查询改约3次内的，包括3次
        sql.append(" and t1.reinventions <= 4");
        // 只查询最新的预约(非重新预约)
        sql.append(" and t1.parent_id is null");
        // 查询未删除的
        sql.append(" and t1.delete_flag = 0");
        // 按预约时间排序
        sql.append(" order by t1.appointment");
        html.append("今日接待任务预览<br/>");
        List<Map> maps = sqlManager.execute(new SQLReady(sql.toString()), Map.class);
        html.append("<table width=\"600px\" border=\"2\" style=\"border:solid #fff; border-width:1px 0px 0px 1px;\">");
        html.append("<tr>");
        html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
        html.append("患者");
        html.append("</td>");
        html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
        html.append("病历号");
        html.append("</td>");
        html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
        html.append("预约时间");
        html.append("</td>");
        html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
        html.append("预约备注");
        html.append("</td>");
        html.append("</tr>");
        for (Map map : maps) {
            html.append("<tr>");
            html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
            html.append(map.get("patientName").toString());
            html.append("</td>");
            html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
            html.append(map.get("medicalNumber").toString());
            html.append("</td>");
            html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
            html.append(map.get("appointment").toString());
            html.append("</td>");
            html.append("<td style=\"border:solid #fff; border-width:0px 1px 1px 0px;\">");
            html.append(map.get("note").toString());
            html.append("</td>");
            html.append("</tr>");
        }
        html.append("</table>");
        html.append("总计：" + maps.size());
        // 发送系统消息
        try {
            JSONObject result = new JSONObject();
            JSONObject data = new JSONObject();
            data.put("system", true);
            data.put("id", -1);
            data.put("type", "friend");
            data.put("content", html.toString());
            result.put("emit", "chatMessage");
            result.put("data", data);
            List<Long> clientIds = new ArrayList<>();
            clientIds.add(user.getId());
            WebSocketServer.send(result, clientIds);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @Override
    @Transactional
    public void doConfirm(Long id) {
        StringBuilder sql = new StringBuilder("update business_reservation set reinventions = 0 where id = :id");
        Query nq = em.createNativeQuery(sql.toString());
        nq.setParameter("id", id);
        nq.executeUpdate();
    }
}
